﻿using System;
using System.Data;
using personalplaner.business.common;
using personalplaner.core.sql;
using personalplaner.business.enums;
using System.Collections.Generic;
using personalplaner.business.stamm;

namespace personalplaner.dataprovider.services
{
	public partial class CommonDataService
	{
		#region Get Methods

		/// <summary>
		/// [wert,textwert]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="optionType"></param>
		/// <returns>[wert,textwert]</returns>
		public IEnumerable<OptionValue> GetOptions(DatabaseSettings settings, enOptionType optionType)
		{
			var str = string.Format("select wert = wert, textwert = textwert from Options where Options.typ = '{0}' order by wert", optionType.ToString());

			var table = SimpleQuerry.ExecuteForDataTable(settings, str);

			if (table.Rows.Count == 0)
				return null;

			try
			{
				return m_optionseData(table, optionType);
			}
			catch (Exception e)
			{
				throw new Exception("GetOptions caused an error", e);
			}
		}


		/// <summary>
		/// Creates a list containing all months that can be planed in from the active year
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="mandid"></param>
		/// <returns></returns>
		public IEnumerable<PlanMonth> GetActiveMonths(DatabaseSettings settings, string mandid)
		{
			var querry = new Querry(settings,"get_activemonths");
			querry.AddParameter("@mandid", mandid);

			var table = querry.ExecuteForDataTable();

			if (table.Rows.Count == 0)
				return null;

			try
			{
				return m_ActiveMonthsData(table);
			}
			catch (Exception e)
			{
				throw new Exception("GetActiveMonths caused an error", e);
			}
		}

		/// <summary>
		/// Creates a list containing all months that are planed in a given year
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="yearid"></param>
		/// <returns></returns>
		public IEnumerable<PlanMonth> GetPlanedMonths(DatabaseSettings settings, int yearid)
		{
			var str = string.Format("select atid = ATID, name = Name, typwert = typwert, von = Von, bis = Bis, jahrid = JahrID, tage = Tage from ArbeitsTage where typ = 1 and JahrID = {0}", yearid);
			var table = SimpleQuerry.ExecuteForDataTable(settings, str);

			if (table.Rows.Count == 0)
				return null;

			try
			{
				return m_ActiveMonthsData(table);
			}
			catch (Exception e)
			{
				throw new Exception("GetPlanedMonths caused an error", e);
			}
		}

		/// <summary>
		/// Gets the amount of schemas that are contained
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="mandid"></param>
		/// <returns></returns>
		public int GetSchemaCount(DatabaseSettings settings, string mandid)
		{
			var str = string.Format("select * from Schemas where mandid = '{0}' and Status = 1", mandid);

			var table = SimpleQuerry.ExecuteForDataTable(settings, str);

			return table.Rows.Count;
		}
		
		public bool CheckIfOpenYearExists(DatabaseSettings settings)
		{
			var dv = SimpleQuerry.ExecuteForDataTable(settings, "select JahrID, Jahr from PlanungsJahr where Status = 1");

			if (dv.Rows.Count > 0 && !string.IsNullOrEmpty(dv.Rows[0][0].ToString()))
			{
				return true;
			}

			return false;
		}

		public int GetActiveYearId(DatabaseSettings settings/*, string mandid*/)
		{
            //var table = SimpleQuerry.ExecuteForDataTable(settings, string.Format("select jahrid = JahrID, Jahr from PlanungsJahr where Status = 1 and mandid = '{0}'", mandid));
            var table = SimpleQuerry.ExecuteForDataTable(settings, string.Format("select jahrid = JahrID from PlanungsJahr where Status = 1"));

			if (table.Rows.Count > 0 && table.Columns.Contains("jahrid"))
			{
				try
				{
					return table.Rows[0].Field<int>("jahrid");
				}
				catch (Exception)
				{
					return 0;
				}
			}

			return 0;
		}

		public int GetActiveYear(DatabaseSettings settings/*, string mandid*/)
		{
            //var table = SimpleQuerry.ExecuteForDataTable(settings, string.Format("select jahr = Jahr from PlanungsJahr where Status = 1 and mandid = '{0}'", mandid));
            var table = SimpleQuerry.ExecuteForDataTable(settings, string.Format("select jahr = Jahr from PlanungsJahr where Status = 1"));

			if (table.Rows.Count > 0 && table.Columns.Contains("jahr"))
			{
				try
				{
					return table.Rows[0].Field<int>("jahr");
				}
				catch (Exception)
				{
					return 0;
				}
			}

			return 0;
		}

        /// <summary>
        /// Gets the mandid from the database
        /// </summary>
        /// <param name="settings"></param>
        /// <returns></returns>
        public string GetMandid(DatabaseSettings settings)
        {
            var table = SimpleQuerry.ExecuteForDataTable(settings, string.Format("select mandid = mandid, name = name from mandant where active = 1"));

            if (table.Rows.Count > 0 && table.Columns.Contains("mandid"))
            {
                try
                {
                    // get the newest mandid
                    return table.Rows[table.Rows.Count - 1].Field<string>("mandid");
                }
                catch (Exception exp)
                {
                    personalplaner.core.errorhandling.Logger.ToXML("Mandid Error", "GetMandid", exp.Message);
                    personalplaner.core.errorhandling.ActionLogger.LoggMessage(core.errorhandling.MessageType.DATA_ERROR, string.Format("The Method CommonDataService.GetMandid(...) returned an exception : {0}", exp.Message));
                }
            }

            return "";
        }

        /// <summary>
        /// gets a list of all mandanten
        /// </summary>
        /// <param name="settings"></param>
        /// <returns></returns>
        public IEnumerable<Mandant> GetMandanten(DatabaseSettings settings)
        {
            var table = SimpleQuerry.ExecuteForDataTable(settings, string.Format("select mandid = mandid, name = name, beschreibung = beschreibung, active = active from mandant"));

            if (table.Rows.Count == 0)
                return null;

            try
            {
                return m_MandantenData(table);
            }
            catch (Exception e)
            {
                throw new Exception("GetMandanten caused an error", e);
            }
        }
        
		public IEnumerable<Feiertag> GetFeiertage(DatabaseSettings settings, int year)
		{
			//var str = string.Format("select Name, Von, Bis, Tage, ATID from ArbeitsTage where Typ = 2 and (datepart(year,von) = " + year + " or datepart(year,bis) = " + year + ")");
			var str = string.Format("select name = Name, von = Von, bis = Bis, jahrid = JahrID, atid = ATID, tage = Tage from ArbeitsTage where Typ = 2 and (datepart(year,von) = {0} or datepart(year,bis) = {0})", year);
			var table = SimpleQuerry.ExecuteForDataTable(settings, str);

			if (table.Rows.Count == 0)
				return null;

			try
			{
				return m_FeiretageData(table);
			}
			catch (Exception e)
			{
				throw new Exception("GetFeiertage caused an error", e);
			}
		}

		/// <summary>
		/// Gets the highest year that a plan was created in
		/// </summary>
		/// <param name="settings"></param>
		/// <returns></returns>
		public int GetMaxYear(DatabaseSettings settings)
		{
			var table = SimpleQuerry.ExecuteForDataTable(settings, "select count = max(Jahr) from PlanungsJahr");

			if (table.Rows.Count > 0 && table.Columns.Contains("count"))
				return table.Rows[0].Field<int>("count");

			return 0;
		}

		/// <summary>
		/// Gets a list of years that have been created
		/// </summary>
		/// <param name="settings"></param>
		/// <returns></returns>
		public IEnumerable<PlanYear> GetActiveYears(DatabaseSettings settings)
		{
			//var table = SimpleQuerry.ExecuteForDataTable(settings, "select jahr = Jahr from PlanungsJahr where Status > 1");
			var table = SimpleQuerry.ExecuteForDataTable(settings, "select jahrid = JahrID, jahr = Jahr, von, bis, status = Status from PlanungsJahr order by jahr desc");

			if (table.Rows.Count == 0)
				return null;

			try
			{
				return m_ExistingYearsData(table);
			}
			catch (Exception e)
			{
				throw new Exception("GetActiveYears caused an error", e);
			}
		}

        public int GetActiveJahrId(DatabaseSettings settings, string mandid)
        {
            var str = string.Format("select jahrid = JahrID from PlanungsJahr where Status = 1 and mandid = '{0}'", mandid);
            var table = SimpleQuerry.ExecuteForDataTable(settings, str);

            if (table.Rows.Count == 0 || !table.Columns.Contains("jahrid"))
                return 0;

            try
            {
                return table.Rows[0].Field<int>("jahrid");
            }
            catch (Exception e)
            {
                throw new Exception("GetActiveJahrId caused an error", e);
            }
        }

        public int GetActiveJahr(DatabaseSettings settings, string mandid)
        {
            var str = string.Format("select jahr = Jahr from PlanungsJahr where Status = 1 and mandid = '{0}'", mandid);
            var table = SimpleQuerry.ExecuteForDataTable(settings, str);

            if (table.Rows.Count == 0 || !table.Columns.Contains("jahr"))
                return 0;

            try
            {
                return table.Rows[0].Field<int>("jahr");
            }
            catch (Exception e)
            {
                throw new Exception("GetActiveJahr caused an error", e);
            }
        }

		#endregion

		#region Set Methods

		/// <summary>
		/// Saves hours in a month
		///  - This is the old method calling the old proc...
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="yearid">yearid</param>
		/// <param name="typwert">typwert of month from ArbeitsTage</param>
		/// <param name="days">days to work in month</param>
		[Obsolete("use SaveMonthDays(DatabaseSettings settings, int atid, double days) instead",false)]
		public bool SaveMonthDays(DatabaseSettings settings, int yearid, int typwert, double days)
		{
			var stp = new Querry(settings, "upd_arbeitstage_tage");

			stp.AddParameter("@jahrid", yearid);
			stp.AddParameter("@typwert", typwert);
			stp.AddParameter("@tage", days);

			return stp.Execute(true);
		}

		/// <summary>
		/// Saves hours in a month
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="atid">atid</param>
		/// <param name="days">days to work in month</param>
		public bool SaveMonthDays(DatabaseSettings settings, int atid, double days)
		{
			var stp = new Querry(settings, "upd_tageimmonat");

			stp.AddParameter("@@atid", atid);
			stp.AddParameter("@tage", days);

			return stp.Execute(true);
		}

		/// <summary>
		/// saves a new feiertag and returns the atid
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="feiertag"></param>
		/// <returns></returns>
		public int SaveFeiertag(DatabaseSettings settings, Feiertag feiertag)
		{
			var stp = new Querry(settings, "set_arbeitstage");

			stp.AddParameter("@jahr", feiertag.Von.Year);
			stp.AddParameter("@name", feiertag.Name);
			stp.AddParameter("@typwert", 0);
			stp.AddParameter("@von", feiertag.Von.ToString("yyyyMMdd"));
			stp.AddParameter("@bis", feiertag.Von.ToString("yyyyMMdd"));
			stp.AddParameter("@typ", 2); //1: Monat   2: Feiertag
			stp.AddParameter("@tage", feiertag.Tage);
            stp.AddParameter("@mandid", "");

			var retval = stp.AddParameter("@retval", 0, ParameterDirection.Output);

			stp.Execute();

			return (int)retval.Value;
		}

        public bool CreateNewYear(DatabaseSettings settings, string mandid, int year)
        {
            var stp = new Querry(settings, "set_planungsjahr");

            stp.AddParameter("@jahr", year);
            stp.AddParameter("@mandid", mandid);
            stp.AddParameter("@von", string.Format("{0}0101",year));
            stp.AddParameter("@bis", string.Format("{0}1231", year));

            if (!stp.Execute(true))
                return false;

            var dv = SimpleQuerry.ExecuteForDataTable(settings, "select wert, textwert from Options where typ = 'Monat' order by wert");
            int month = 0;
            string name = "";

            for (int i = 0; i < dv.Rows.Count; i++)
            {
                try
                {
                    month = dv.Rows[i].Field<int>("wert");
                    name = dv.Rows[i].Field<string>("textwert");

                }
                catch (Exception exp)
                {
                    personalplaner.core.errorhandling.Logger.ToXML("Year Error", "CreateNewYear", exp.Message);
                }
                finally
                {
                    SetNewMonth(settings, year, month, name, new DateTime(year, month, 1), new DateTime(year, month, DateTime.DaysInMonth(year, month)), mandid);
                }
            }
            
            return true;
        }

        /// <summary>
        /// Saves a new Month in the Database
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="name"></param>
        /// <param name="von"></param>
        /// <param name="bis"></param>
        public bool SetNewMonth(DatabaseSettings settings, int year, int month, string name, DateTime von, DateTime bis, string mandid)
        {
            var stp = new Querry(settings, "set_arbeitstage");

            stp.AddParameter("@jahr", year);
            stp.AddParameter("@name", name);
            stp.AddParameter("@typwert", month);
            stp.AddParameter("@von", von);
            stp.AddParameter("@bis", bis);
            stp.AddParameter("@typ", 1); //1: Monat   2: Feiertag
            stp.AddParameter("@tage", 0);
            stp.AddParameter("@mandid", mandid);

            try
            {
                return stp.Execute(true);
            }
            catch (Exception e)
            {
                personalplaner.core.errorhandling.Logger.ToXML("set_arbeitstage", "SetNewMonth", e.Message);
            }
            return false;
        }

        public bool SetPersonZeiten(DatabaseSettings settings, int oldyearid)
        {
            // Personen in personzeiten auf neues Jahr nachführen
            var dv = SimpleQuerry.ExecuteForDataTable(settings, "select prsid = p.PrsId from Person p inner join Arbeitspensum ap on ap.PrsID = p.PrsId and ap.Bis > getdate() where p.PersonTyp = 1");
            for (int i = 0; i < dv.Rows.Count; i++)
            {
                Querry stp = new Querry(settings, "set_personzeitennew");

                stp.AddParameter("@prsid", dv.Rows[i].Field<int>("prsid"));
                stp.AddParameter("@oldjahrid", oldyearid);
                stp.Execute();
            }

            return true;
        }

        /// <summary>
        /// Adds a mandant to the database
        /// </summary>
        /// <param name="mandid">shortcut/primary id for the mandant.</param>
        /// <param name="name">name of the mandant</param>
        /// <param name="description">description of the mandant</param>
        public bool SetMandant(DatabaseSettings settings, string mandid, string name, string description)
        {
            var stp = new Querry(settings, "set_mandant");

            stp.AddParameter("@mandid", mandid);
            stp.AddParameter("@name", name);
            stp.AddParameter("@beschreibung", description);

            return stp.Execute(true);
        }

        public bool SetActiveMandant(DatabaseSettings settings, string mandid)
        {
            if (SimpleQuerry.Execute(settings, string.Format("update mandant set active = 0 where mandid <> '{0}'", mandid)))
            {
                return SimpleQuerry.Execute(settings, string.Format("update mandant set active = 1 where mandid = '{0}'", mandid));
            }

            return false;
        }

		#endregion

		#region Update Methods

		public bool UpdateFeiertag(DatabaseSettings settings, Feiertag feiertag)
		{
			var stp = new Querry(settings, "upd_arbeitstage_feiertag");

			stp.AddParameter("@name", feiertag.Name);
			stp.AddParameter("@von", feiertag.Von.ToString("yyyyMMdd"));
			stp.AddParameter("@bis", feiertag.Von.ToString("yyyyMMdd"));
			stp.AddParameter("@tage", feiertag.Tage);
			stp.AddParameter("@atid", feiertag.AtId);

			return stp.Execute();
		}

		/// <summary>
		/// Reactivates an old year and deactivates all others
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="year"></param>
		/// <returns></returns>
		public bool ReactivateYear(DatabaseSettings settings, int year)
		{
			var stp = new Querry(settings, "upd_planungsjahr_jahr");

			stp.AddParameter("@jahr", year);

			return stp.Execute();
		}

		#endregion

        #region Del Methods

        public bool DelFeiertag(DatabaseSettings settings, int atid)
        {
            var str = string.Format("delete from ArbeitsTage where ATID = {0}", atid);

            return SimpleQuerry.Execute(settings, str);
        }

        #endregion
	}
}
